using System.Data;
using Dapper;
using Microsoft.Data.SqlClient;

namespace CoreProject.Repository
{
  /// <summary>
  /// Dapper 工具类
  /// </summary>
  public static class DapperHelper
  {

    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string connectionString = "Data Source=.;Initial Catalog=Account;user id=sa;password=123;Max Pool Size=512;Min Pool Size=5;TrustServerCertificate=true";


    /// <summary>
    /// 查询数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static IEnumerable<dynamic> QueryDynamic(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.Query<dynamic>(sql, param).ToList();
      }
    }

    /// <summary>
    /// 查询第一个数据没有返回默认值
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static dynamic QueryFirstOrDefault(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.QueryFirstOrDefault<dynamic>(sql, param);
      }
    }

    /// <summary>
    /// 查询列表
    /// </summary>
    /// <param name="sql">查询的sql</param>
    /// <param name="param">替换参数</param>
    /// <returns></returns>
    public static List<T> Query<T>(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.Query<T>(sql, param).ToList();
      }
    }

    /// <summary>
    /// 查询列表
    /// </summary>
    /// <param name="sql">查询的sql</param>
    /// <param name="param">替换参数</param>
    /// <returns></returns>
    public static async Task<List<T>> QueryAsync<T>(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        var data = await con.QueryAsync<T>(sql, param);

        return data.ToList();
      }
    }

    /// <summary>
    /// 查询第一个数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static T QueryFirst<T>(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.QueryFirst<T>(sql, param);
      }
    }

    /// <summary>
    /// 查询第一个数据没有返回默认值
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static T QueryFirstOrDefault<T>(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.QueryFirstOrDefault<T>(sql, param);
      }
    }

    /// <summary>
    /// 查询单条数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static T QuerySingle<T>(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.QuerySingle<T>(sql, param);
      }
    }

    /// <summary>
    /// 查询单条数据没有返回默认值
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static T QuerySingleOrDefault<T>(string sql, object param = null)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.QuerySingleOrDefault<T>(sql, param);
      }
    }

    /// <summary>
    /// 增删改,返回影响的行数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns>影响的行数</returns>
    public static int Execute(string sql, object param)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.Execute(sql, param);
      }
    }

    /// <summary>
    /// Reader获取数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static IDataReader ExecuteReader(string sql, object param)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.ExecuteReader(sql, param);
      }
    }

    /// <summary>
    /// Scalar获取数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static object ExecuteScalar(string sql, object param)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.ExecuteScalar(sql, param);
      }
    }

    /// <summary>
    /// Scalar获取数据
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static T ExecuteScalar<T>(string sql, object param)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        return con.ExecuteScalar<T>(sql, param);
      }
    }

    /// <summary>
    /// 带参数的存储过程
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="param"></param>
    /// <returns></returns>
    public static List<T> ExecutePro<T>(string proc, object param)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        List<T> list = con.Query<T>(proc,
            param,
            null,
            true,
            null,
            CommandType.StoredProcedure).ToList();
        return list;
      }
    }


    /// <summary>
    /// 事务1 - 全SQL
    /// </summary>
    /// <param name="sqlarr">多条SQL</param>
    /// <param name="param">param</param>
    /// <returns></returns>
    public static int ExecuteTransaction(string[] sqlarr)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        using (var transaction = con.BeginTransaction())
        {
          try
          {
            int result = 0;
            foreach (var sql in sqlarr)
            {
              result += con.Execute(sql, null, transaction);
            }

            transaction.Commit();
            return result;
          }
          catch (Exception ex)
          {
            transaction.Rollback();
            return 0;
          }
        }
      }
    }

    /// <summary>
    /// 事务2 - 声明参数
    ///demo:
    ///dic.Add("Insert into Users values (@UserName, @Email, @Address)",
    ///        new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
    /// </summary>
    /// <param name="Key">多条SQL</param>
    /// <param name="Value">param</param>
    /// <returns></returns>
    public static int ExecuteTransaction(Dictionary<string, object> dic)
    {
      using (SqlConnection con = new SqlConnection(connectionString))
      {
        using (var transaction = con.BeginTransaction())
        {
          try
          {
            int result = 0;
            foreach (var sql in dic)
            {
              result += con.Execute(sql.Key, sql.Value, transaction);
            }

            transaction.Commit();
            return result;
          }
          catch (Exception ex)
          {
            transaction.Rollback();
            return 0;
          }
        }
      }
    }
  }
}
